#include "sqlserver.h"
#include "ui_sqlserver.h"

SqlServer::SqlServer(QWidget *parent)
    : QMainWindow(parent)
    , ui(new Ui::SqlServer)
{
    ui->setupUi(this);
    setWindowTitle("SQL Server Monitor");

    tcpServer = new QTcpServer(this);
    connect(tcpServer, &QTcpServer::newConnection, this, &SqlServer::newConnection);

    if (!initializeDatabase()) {
        logMessage("Database initialization failed!");
        return;
    }

    if (!tcpServer->listen(QHostAddress::Any, 12345)) {
        logMessage("Server could not start: " + tcpServer->errorString());
    } else {
        logMessage("Server started on port 12345");
    }
}

SqlServer::~SqlServer()
{
    if (connectionCheckTimer) {
        connectionCheckTimer->stop();
        delete connectionCheckTimer;
    }

    QMutexLocker locker(&poolMutex);
    while (!connectionPool.isEmpty()) {
        QSqlDatabase db = connectionPool.pop();
        if (db.isOpen()) {
            db.close();
        }
        QSqlDatabase::removeDatabase(db.connectionName());
    }

    if (tcpServer->isListening()) {
        tcpServer->close();
    }

    delete ui;
}

void SqlServer::logMessage(const QString &message)
{
    QString logEntry = QDateTime::currentDateTime().toString("[yyyy-MM-dd hh:mm:ss] ") + message;
    ui->logText->append(logEntry);
    qDebug() << logEntry;
}

bool SqlServer::initializeDatabase()
{

    QString dbPath = "E:/QT-projects/SnailIOTMonitoring/wn_icp.db";
    if (!QFile::exists(dbPath)) {
        logMessage("Error: Database file not found at " + dbPath);
        return false;
    }

    if (!QSqlDatabase::isDriverAvailable("QSQLITE")) {
        logMessage("Error: SQLite driver not loaded");
        return false;
    }

    QMutexLocker locker(&poolMutex);
    for (int i = 0; i < 5; ++i) {
        if (!createConnection(i)) {
            return false;
        }
    }

    connectionCheckTimer = new QTimer(this);
    connect(connectionCheckTimer, &QTimer::timeout, this, &SqlServer::checkConnections);
    connectionCheckTimer->start(30000);

    return true;
}

bool SqlServer::createConnection(int id)
{
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE", QString("ServerConnection_%1").arg(id));
    db.setDatabaseName("E:/QT-projects/SnailIOTMonitoring/wn_icp.db");
    if (!db.open()) {
        logMessage(QString("Failed to open database connection %1: %2").arg(id).arg(db.lastError().text()));
        return false;
    }
    connectionPool.push(db);
    logMessage(QString("Created database connection (ID: %1)").arg(id));
    return true;
}

QSqlDatabase SqlServer::getConnection()
{
    QMutexLocker locker(&poolMutex);

    while (!connectionPool.isEmpty()) {
        QSqlDatabase db = connectionPool.pop();
        if (db.isOpen() && db.isValid()) {
            return db;
        }
        if (db.isOpen()) db.close();
        QSqlDatabase::removeDatabase(db.connectionName());
    }

    static int counter = connectionPool.size();
    if (counter < maxPoolSize) {
        if (createConnection(counter++)) {
            return connectionPool.pop();
        }
    }

    logMessage("WARNING: No available database connections!");
    return QSqlDatabase();
}

void SqlServer::releaseConnection(QSqlDatabase &db)
{
    if (!db.isValid()) {
        if (db.isOpen()) db.close();
        QSqlDatabase::removeDatabase(db.connectionName());
        return;
    }

    QMutexLocker locker(&poolMutex);
    connectionPool.push(db);
}

QString SqlServer::executeQuery(const QString &sql)
{
    QSqlDatabase db = getConnection();
    if (!db.isValid()) {
        return "Error: No database connection available";
    }

    // 分割多条SQL语句
    QStringList queries = sql.split(';', QString::SkipEmptyParts);
    QString lastResult;

    db.transaction();

    foreach (const QString &queryStr, queries) {
        QString trimmedQuery = queryStr.trimmed();
        if (trimmedQuery.isEmpty()) continue;

        QSqlQuery query(db);
        if (!query.exec(trimmedQuery)) {
            db.rollback();
            QString error = query.lastError().text();
            releaseConnection(db);
            return "Error: " + error;
        }

        // 处理SELECT查询结果
        if (trimmedQuery.startsWith("SELECT", Qt::CaseInsensitive)) {
            QString result;
            while (query.next()) {
                for (int i = 0; i < query.record().count(); ++i) {
                    if (i > 0) result += " | ";
                    result += query.value(i).toString().trimmed();
                }
                result += "\n";
            }
            lastResult = result.isEmpty() ? "No results found" : result;
        }
        // 处理INSERT操作
        else if (trimmedQuery.startsWith("INSERT", Qt::CaseInsensitive)) {
            if (query.lastInsertId().isValid()) {
                lastResult = "InsertSuccess:" + query.lastInsertId().toString();
            } else {
                lastResult = "InsertSuccess:0";
            }
        }
    }

    db.commit();
    releaseConnection(db);
    return lastResult;
}


void SqlServer::newConnection() {
    QTcpSocket *clientSocket = tcpServer->nextPendingConnection();
    // 移除初始的 AUTH_REQUIRED 发送（改为在协议中隐式处理）
    connect(clientSocket, &QTcpSocket::readyRead, this, &SqlServer::readData);
    logMessage("New client connected");
}

void SqlServer::readData() {
    QTcpSocket* socket = qobject_cast<QTcpSocket*>(sender());
    while (socket->canReadLine()) {
        QString request = QString::fromUtf8(socket->readLine()).trimmed();
        logMessage(request);
        QString response;

        if (request.startsWith("AUTH:")) {
            response = handleAuthRequest(request.mid(5));  // 处理认证
        } else {
            response = executeQuery(request);  // 处理普通SQL
            logMessage(response);
        }

        // 确保响应是单行且不含无关内容
        socket->write(response.toUtf8() + "\n");
        socket->flush();
    }
}

QString SqlServer::handleAuthRequest(const QString &sql)
{
    qDebug() << "Processing auth request:" << sql;

    QSqlDatabase db = getConnection();
    if (!db.isValid()) {
        return "AUTH_ERROR:NO_DB_CONNECTION";
    }

    QSqlQuery query(db);
    if (!query.exec(sql)) {
        QString error = query.lastError().text();
        releaseConnection(db);
        return "AUTH_ERROR:" + error;
    }

    if (!query.next()) {
        releaseConnection(db);
        return "AUTH_ERROR:USER_NOT_FOUND";
    }

    QString result = "AUTH_SUCCESS:" + query.value(0).toString();
    releaseConnection(db);
    return result;
}

void SqlServer::clientDisconnected()
{
    QTcpSocket *clientSocket = qobject_cast<QTcpSocket*>(sender());
    if (clientSocket) {
        logMessage("Client disconnected: " + clientSocket->peerAddress().toString());
        clientSocket->deleteLater();
    }
}

void SqlServer::checkConnections()
{
    QMutexLocker locker(&poolMutex);

    int initialCount = connectionPool.size();
    QStack<QSqlDatabase> validConnections;

    while (!connectionPool.isEmpty()) {
        QSqlDatabase db = connectionPool.pop();
        if (db.isOpen() && db.isValid()) {
            validConnections.push(db);
        } else {
            if (db.isOpen()) db.close();
            QSqlDatabase::removeDatabase(db.connectionName());
            logMessage("Removed invalid database connection");
        }
    }

    connectionPool = validConnections;

    while (connectionPool.size() < 5 && connectionPool.size() < maxPoolSize) {
        if (!createConnection(connectionPool.size())) {
            break;
        }
    }

    if (initialCount != connectionPool.size()) {
        logMessage(QString("Connection pool status: %1/%2 connections available")
                 .arg(connectionPool.size()).arg(maxPoolSize));
    }
}
